﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data;

namespace AsiaSocietyExceltoDatabase
{
    class AsiaSocietyParser
    {
     //   private string city;
  //      private string IndexName;
   //     private DateTime date;
   //     private float value;
        private string filename;
        private int countRow;
      //  private string[] rows;
        private int countCity;
        private string[] citys;
        private int spaceCount;
        private int[] spacesPerCity;
        private string[] indexs;
        private int indexCount;
     //   private string[] realData;
        private DataTable dt;

        public DataTable getDataTable
        {
            get
            {
                return dt;
            }
        }



        public AsiaSocietyParser(string file)
        {
            this.filename = file;

            CreateTable();
            ReadFileIntoTable(filename);
        }

        private void CreateTable()
        {
            dt = new DataTable();
            dt.Columns.Add("CityID", typeof(int));
            dt.Columns.Add("PM25ID", typeof(string));
            dt.Columns.Add("PM10ID", typeof(string));
            dt.Columns.Add("API", typeof(string));
            dt.Columns.Add("USPM25ID", typeof(string));
            dt.Columns.Add("USPM10ID", typeof(string));
            dt.Columns.Add("AQI", typeof(string));
            dt.Columns.Add("Date", typeof(string));
        }


        private void ReadFileIntoTable(string file)
        {
            CountLinesInFile(file);
            
            int i = 0;
            int x = 0;
            countCity = 0;
            spaceCount = 0;

            StreamReader fileReader = new StreamReader(file);

            string strLine;
          //  string delimiter = ",";

            strLine = fileReader.ReadLine();

            while (!(strLine == null))
            {
                string[] strArray = strLine.Split(',');
                
                ////////////////////////////////////////////////////////////////////////////////////////////
                if (i == 0)
                {
                    foreach (string word in strArray)
                    {
                        if ((word != "CITY") && (word != ""))
                        {
                            countCity++;
                        }
                    }

                    citys = new string[countCity];
                    spacesPerCity = new int[countCity];

                    foreach (string word in strArray)
                    {
                        if (word == "")
                        {
                            spaceCount++;
                        }
                        else if ((word != "CITY") && (word != ""))
                        {
                            {
                                citys[x] = word;
                                spacesPerCity[x] = (spaceCount + 1);
                                spaceCount = 0;
                                x++;
                            }
                        }
                    }
                }
                /////////////////////////////////////////////////////////////////////////////////////////////////
                if (i == 1)
                {
                    x = 0;

                    foreach (string word in strArray)
                    {
                        if ((word != "DATA NAME") && (word != ""))
                        {
                            indexCount++;
                        }
                    }

                    indexs = new string[indexCount];

                    foreach (string word in strArray)
                    {
                        if ((word != "DATA NAME") && (word != ""))
                        {
                            {
                                indexs[x] = word;
                                x++;
                            }
                        }
                    }
                }
                ////////////////////////////////////////////////////////////////////////////////////////////////

                if (i > 1)
                {
               //     rows = new string[strArray.Length];
                    x = 0;


                    int iDCity = 0;
                    int iDOldCity = 0;
                    string PM25 = "0";
                    string PM10 = "0";
                    string API = "0";
                    string USPM25 = "0";
                    string USPM10 = "0";
                    string AQI = "0";


                    for (int r = 1; r < strArray.Length; r++)
                    {


                            iDCity = CheckForCityID(r);

                            if (iDCity != iDOldCity && iDOldCity != 0 && iDCity != 0)
                            {
                              //  int enableCity = PM10 + PM25 + USPM10 + USPM25 + API + AQI;

                              //  if(enableCity > 0)
                               //     dt.Rows.Add("N", iDOldCity, PM25, PM10, API, USPM25, USPM10, AQI, Convert.ToDateTime(strArray[0]));
                              //  else
                                dt.Rows.Add(iDOldCity, PM25, PM10, API, USPM25, USPM10, AQI, Convert.ToDateTime(strArray[0].ToString()));
                                
                                PM25 = "0";
                                PM10 = "0";
                                API = "0";
                                USPM25 = "0";
                                USPM10 = "0";
                                AQI = "0";

                                iDOldCity = iDCity;
                            }

                            if (iDOldCity == 0 && iDCity != 0)
                            {
                                iDOldCity = iDCity;
                                PM25 = "0";
                                PM10 = "0";
                                API = "0";
                                USPM25 = "0";
                                USPM10 = "0";
                                AQI = "0";
                            }
                            string value;
                            try
                            {
                                value = strArray[r].ToString();
                                if (value == "")
                                    value = "0";
                            }
                            catch
                            {
                                value = "0";
                            }
                            if (indexs[r-1] == "API")
                            {
                                API = value;
                            }
                            else if (indexs[r - 1] == "PM2.5")
                            {
                                PM25 = value;
                            }
                            else if (indexs[r - 1] == "PM10")
                            {
                                PM10 = value;
                            }
                            else if (indexs[r - 1] == "AQI")
                            {
                                AQI = value;
                            }
                            else if (indexs[r - 1] == "US_PM2.5")
                            {
                                USPM25 = value;
                            }
                            else if (indexs[r - 1] == "US_PM10")
                            {
                                USPM10 = value;
                            }
                    }
                    dt.Rows.Add(iDOldCity, PM25, PM10, API, USPM25, USPM10, AQI, Convert.ToDateTime(strArray[0].ToString()));
                }
                ///////////////////////////////////////////////////////////////////////////////////

                strLine = fileReader.ReadLine();
                i++;
            }
           
        }

        private int CheckForCityID(int r)
        {
            if (r == 1)
            {
                return 3; //Beijing
            }
            else if (r==6)
            {
                return 5; //Guangzhou
            }
            else if (r == 11)
            {
                return 4; //Shanghai
            }
            else if (r == 16)
            {
                return 6; //Hong Kong
            }
            else if (r == 19)
            {
                return 7; //New York
            }
            else if (r == 22)
            {
                return 11; //Chicago
            }
            else if (r == 25)
            {
                return 8; //Phoenix
            }
            return 0;
        }

       



        private void CountLinesInFile(string file)
        {
            int count = 0;
            using (StreamReader r = new StreamReader(file))
            {
                string line;
                while ((line = r.ReadLine()) != null)
                {
                    count++;
                }
            }
            this.countRow = count;
        }



    }
}
